﻿#region

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

#endregion

namespace DataAccessLayer
{
    public static class SupervisorDAL
    {
        private static readonly string ConnectionString =
            ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        /// <summary>
        ///     Register a supervisor
        /// </summary>
        /// <param name="supervisor">Represent's the supervisor</param>
        public static void RegisterSupervisor(Supervisor supervisor)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spAddSupervisor", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@StaffId", supervisor.StaffId);
                command.Parameters.AddWithValue("@SupervisorType", supervisor.SupervisorType);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Get All supervisors
        /// </summary>
        /// <returns>All supervisors</returns>
        public static List<Supervisor> GetAllSupervisors()
        {
            var supervisors = new List<Supervisor>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetAllSupervisors", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var supervisor = new Supervisor
                        {
                            StaffId = Convert.ToInt32(reader["StaffId"]),
                            SupervisorType = reader["SupervisorType"].ToString()
                        };
                        supervisors.Add(supervisor);
                    }
                }
            }
            return supervisors;
        }

        /// <summary>
        ///     Get supervisor type by id
        /// </summary>
        /// <param name="staffId">Represent's the staff id</param>
        /// <returns>Supervisor type</returns>
        public static string GetSupervisorTypeById(int staffId)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetSupervisorTypeById", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@StaffId", staffId);

                connection.Open();
                var type = command.ExecuteScalar();

                return type != null ? type.ToString() : null;
            }
        }

        /// <summary>
        ///     Edit supervisor's details
        /// </summary>
        /// <param name="supervisor">Represent's the supervisor</param>
        public static void EditSupervisor(Supervisor supervisor)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spEditSupervisor", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@StaffId", supervisor.StaffId);
                command.Parameters.AddWithValue("@SupervisorType", supervisor.SupervisorType);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Get All supervisors by subsidiary id and supervisor type
        /// </summary>
        /// <param name="subsidiaryId">Represent's the subsidiary id</param>
        /// <param name="supervisorType">Represent's the supervisor type</param>
        /// <returns>All supervisors</returns>
        public static List<Supervisor> GetAllSupervisors(byte subsidiaryId, string supervisorType)
        {
            var supervisors = new List<Supervisor>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetSupervisorsBySubsidiaryAndType", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@SubsidiaryId", subsidiaryId);
                command.Parameters.AddWithValue("@SupervisorType", supervisorType);

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var supervisor = new Supervisor
                        {
                            StaffId = Convert.ToInt32(reader["StaffId"]),
                            FirstName = reader["FirstName"].ToString(),
                            LastName = reader["LastName"].ToString(),
                            EMail = reader["EMail"].ToString(),
                            Address = reader["Address"].ToString(),
                            TelephoneNo = reader["TelephoneNo"].ToString(),
                            Type = reader["Type"].ToString(),
                            SubsidiaryId = subsidiaryId,
                            SupervisorType = supervisorType
                        };

                        supervisors.Add(supervisor);
                    }
                }
            }
            return supervisors;
        }
    }
}